package br.ufrj.neg.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;

import br.ufrj.neg.bean.Idioma;
import br.ufrj.neg.bean.NegociacaoElementoBean;

public class NegociacaoElementoModel {
	
	//private static Logger log = Logger.getLogger("rootLogger");
	
	public boolean criaNegociacaoElemento(int idNegocio) {
		PreparedStatement ps=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "INSERT INTO NEGOCIACAO_ELEMENTO(NEGO_CD_ID,ELNE_CD_ID,NEEL_TX_OBSERVACAO)" +
					"select "+idNegocio+", ELNE_CD_ID , '' FROM ELEMENTO_NEGOCIACAO";
			ps= conn.prepareStatement(sql);
			ps.executeUpdate();
			conn.commit();
			sql = "UPDATE NEGOCIACAO_ELEMENTO SET neel_vl_pior_caso=-1, neel_vl_melhor_caso=-1 where nego_cd_id=? and elne_cd_id in(2,4)";
			ps= conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			ps.executeUpdate();
			conn.commit();
			return true;
		}catch(Exception e){
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	
	}
	
	public Hashtable<Integer,NegociacaoElementoBean> getNegociacaoElemento(int idNegocio) {
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "Select * from negociacao_elemento where nego_cd_id = "+idNegocio+" order by elne_cd_id";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			Hashtable<Integer,NegociacaoElementoBean> nes = new Hashtable<Integer,NegociacaoElementoBean>();
			NegociacaoElementoBean ne;
			while(rs.next()){
				ne=new NegociacaoElementoBean();
				ne.setIdElemento(rs.getInt("ELNE_CD_ID"));
				ne.setIdNegociacao(rs.getInt("NEGO_CD_ID"));
				ne.setMelhorCaso(rs.getInt("NEEL_VL_MELHOR_CASO"));
				ne.setObservacao(rs.getString("NEEL_TX_OBSERVACAO"));
				ne.setPeso(rs.getDouble("NEEL_VL_PESO"));
				ne.setPiorCaso(rs.getInt("NEEL_VL_PIOR_CASO"));
				ne.setIdUnidadeValoracao(rs.getInt("UNVA_CD_ID"));
				nes.put(new Integer(ne.getIdElemento()), ne);
			}
			return nes;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public boolean atualizaNegociacaoElemento(Hashtable<Integer,NegociacaoElementoBean> nes,int idNegocio) {
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		try{
			//verifica se as entradas na tabela negociacao_elemento foram criados ao criar o negocio,
			//caso nao tenham sido, s�o criados apenas os que n�o foram
			verificaNegociacaoElemento(nes,idNegocio);
			
			Enumeration enumeration = nes.elements();
			while (enumeration.hasMoreElements()){
				NegociacaoElementoBean ne = (NegociacaoElementoBean)enumeration.nextElement();
				String sql="update negociacao_elemento set " +
						(ne.getObservacao()!=null?"neel_tx_observacao='"+ne.getObservacao()+"', ":"") +
						"neel_vl_pior_caso="+ne.getPiorCaso()+", "+
						"neel_vl_melhor_caso="+ne.getMelhorCaso()+", "+
						"neel_vl_peso="+ne.getPeso()+
						(ne.getIdUnidadeValoracao()!=0?", unva_cd_id="+ne.getIdUnidadeValoracao()+" ":" ")+
						"where nego_cd_id = "+ne.getIdNegociacao()+" " +
						"and elne_cd_id = "+ne.getIdElemento();
				ps = conn.prepareStatement(sql);
				ps.executeUpdate();
			}
			conn.commit();
			return true;
		}catch(Exception e){
			System.out.println(e);
			return false;
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	private void verificaNegociacaoElemento(Hashtable<Integer,NegociacaoElementoBean> nes, int idNegocio) {
		PreparedStatement ps=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			ArrayList<Integer> c = getNegociacaoElementoIds();
			Iterator it = c.iterator();
			while (it.hasNext()){
				Integer i = (Integer)it.next();
				if (nes.get(i)==null){
					String sql="Insert into negociacao_elemento(nego_cd_id,elne_cd_id,neel_tx_observacao) values("+idNegocio+","+i.intValue()+",'')";
					ps=conn.prepareStatement(sql);
					ps.executeUpdate();
					conn.commit();
				}
			}
		}catch(Exception e){
			System.out.println(e);
		}finally{
			try{ps.close();}catch(Exception e){}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	private ArrayList<Integer> getNegociacaoElementoIds(){
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			ArrayList<Integer> result = new ArrayList<Integer>();
			String sql = "Select elne_cd_id from elemento_negociacao order by elne_cd_id";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				result.add(new Integer(rs.getInt("elne_cd_id")));
			}
			return result;
		}catch(Exception e){
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
		
	}
	
	public int getNumeroElementos(){
		PreparedStatement ps=null;
		ResultSet rs=null;
		Connection conn = ServiceLocator.getConnection();
		try{
			String sql = "Select count(*) as cont from elemento_negociacao ";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if (rs.next()){				
				return rs.getInt("cont");
			}else return -1;
		}catch(Exception e){
			System.out.println(e);
			return -1;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	public String[] getElementos(Idioma id) {
		String[] elementos = { 	id.get("negociacao.preparacao.contexto.tag"),
								id.get("negociacao.preparacao.interesses.tag"), 
								id.get("negociacao.preparacao.opcoes.tag"),
								id.get("negociacao.preparacao.relacionamentos.tag"),
								id.get("negociacao.preparacao.poder.tag"),
								id.get("negociacao.preparacao.comunicacao.tag"),
								id.get("negociacao.preparacao.criterios.tag"),
								id.get("negociacao.preparacao.legitimidade.tag"),
								id.get("negociacao.preparacao.tempo.tag"),
								id.get("negociacao.preparacao.concessoes.tag")
							};
								
		
		return elementos;
	}
}
